﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections;
using System.Data.SqlClient;
using System.Configuration;
using Trying1;


namespace Trying1.Classes
{
    class HouseKeepDBManager
    {
        public static ArrayList GetAllDuty()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from HouseKeeping";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    HouseKeeping h = new HouseKeeping();

                    h.hKeepId = (int)dr["hKeepID"];
                    h.hKeepName = (string)dr["hKeepName"];
                    h.dateOfAssignment = (DateTime)dr["dateOfAssignment"];
                    h.dutyAssigned = (string)dr["dutyAssigned"];
                    result.Add(h);
                }
                dr.Close();
                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }


        public static int insertHouseKeeping(HouseKeeping h)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO HouseKeeping(dateOfAssignment, hKeepName, dutyAssigned)" +
                    " VALUES (@dateOfAssignment,@hKeepName,@dutyAssigned)";
                comm.Parameters.AddWithValue("@dateOfAssignment", h.dateOfAssignment);
                comm.Parameters.AddWithValue("@hKeepName", h.hKeepName);
                comm.Parameters.AddWithValue("@dutyAssigned", h.dutyAssigned);

                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static HouseKeeping GetHkByName(string hKeepName)
        {
            HouseKeeping h = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM HouseKeeping WHERE hKeepName =@hKeepName";
                comm.Parameters.AddWithValue("@hKeepName", hKeepName);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    h = new HouseKeeping();
                    h.hKeepName = (string)dr["hKeepName"];
                    h.hKeepId = (int)dr["hKeepId"];
                    h.dateOfAssignment = (DateTime)dr["dateOfAssignment"];
                    h.dutyAssigned = (string)dr["dutyAssigned"];
                }

                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return h;
        }


        public static int UpdateHouseKeeping(HouseKeeping h)
        {
            int rowsupdated = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE HouseKeeping SET dateOfAssignment=@dateOfAssignment, hKeepName=@hKeepName, dutyAssigned=@dutyAssigned WHERE hKeepId=@hKeepId";
                comm.Parameters.AddWithValue("@hKeepId", h.hKeepId);
                comm.Parameters.AddWithValue("@hKeepName", h.hKeepName);
                comm.Parameters.AddWithValue("@dutyAssigned", h.dutyAssigned);
                comm.Parameters.AddWithValue("@dateOfAssignment", h.dateOfAssignment);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowsupdated;
        }

        public static int DeleteHouseKeeper(string hKeepName)
        {
            int rowsdeleted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE from HouseKeeping WHERE @hKeepName = hKeepName";
                comm.Parameters.AddWithValue("@hKeepName", hKeepName);
                rowsdeleted = comm.ExecuteNonQuery();
            }

            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }

    }
}



